package com.example.fund.util;

import com.example.fund.Constant;

import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper// 继承SQLiteOpenHelper类
{
    // 数据库版本号
    private static final int DATABASE_VERSION = 8;
    // 数据库名
    private static final String DATABASE_NAME = "fund.db";

    public DatabaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

        // 数据库实际被创建是在getWritableDatabase()或getReadableDatabase()方法调用时
        Log.d(Constant.LOG_TAG, "DatabaseHelper Constructor");
        // CursorFactory设置为null,使用系统默认的工厂类
    }
    

    // 继承SQLiteOpenHelper类,必须要覆写的三个方法：onCreate(),onUpgrade(),onOpen()
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        // 调用时间：数据库第一次创建时onCreate()方法会被调用

        // onCreate方法有一个 SQLiteDatabase对象作为参数，根据需要对这个对象填充表和初始化数据
        // 这个方法中主要完成创建数据库后对数据库的操作

        Log.d(Constant.LOG_TAG, "DatabaseHelper onCreate");

        // 构建创建表的SQL语句（可以从SQLite Expert工具的DDL粘贴过来加进StringBuffer中）
        StringBuffer sql = new StringBuffer();
        sql.append("	CREATE TABLE Fund (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	name TEXT,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	pinyin TEXT,	\n");
        sql.append("	grade INTEGER,	\n");
        sql.append("	type TEXT	\n");
        sql.append("	)	\n");
        // 执行创建表的SQL语句
        db.execSQL(sql.toString());
        // 即便程序修改重新运行，只要数据库已经创建过，就不会再进入这个onCreate方法
        sql.setLength(0);
        sql.append("	CREATE TABLE FundNetValue (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	fundNumber TEXT,	\n");
        sql.append("	netValue DOUBLE,	\n");
        sql.append("	volume DOUBLE,	\n");
        sql.append("	date TEXT	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table FundStocks (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	fundnumber TEXT,	\n");
        sql.append("	date TEXT,	\n");
        sql.append("	stocksnumber TEXT,	\n");
        sql.append("	stocksname TEXT,	\n");
        sql.append("	share DOUBLE	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table zzhyfl (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	name TEXT	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table Stocksfl (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	name TEXT,	\n");
        sql.append("	level1 TEXT,	\n");
        sql.append("	level2 TEXT,	\n");
        sql.append("	level3 TEXT,	\n");
        sql.append("	level4 TEXT	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table indexset (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	seq INTEGER,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	name TEXT,	\n");
        sql.append("	refNumber TEXT,	\n");
        sql.append("	defDate TEXT,	\n");
        sql.append("	enable INTEGER	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table indexStocks (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	stocksnumber TEXT,	\n");
        sql.append("	indexnumber TEXT,	\n");
        sql.append("	startDate TEXT,	\n");
        sql.append("	endDate TEXT	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table fundTrade (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	fundNumber TEXT,	\n");
        sql.append("	account TEXT,	\n");
        sql.append("	tradePrice DOUBLE,	\n");
        sql.append("	tradeAmount DOUBLE,	\n");
        sql.append("	tradeRate DOUBLE,	\n");
        sql.append("	tradeQty DOUBLE,	\n");
        sql.append("	date TEXT,	\n");
        sql.append("	type INTEGER	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        String sqlIdx1 = "CREATE INDEX idx_fundNetValue_fundNumber ON fundNetValue(fundNumber)";
        db.execSQL(sqlIdx1.toString());
        
        String sqlIdx2 = "CREATE INDEX idx_fund_number ON fund(number)";
        db.execSQL(sqlIdx2.toString());
        
        String sqlIdx3 = "CREATE INDEX idx_fundNetValue_date ON fundNetValue(date)";
        db.execSQL(sqlIdx3.toString());
        
        String sqlIdx4 = "CREATE INDEX idx_fund_pinyin ON fund(pinyin)";
        db.execSQL(sqlIdx4.toString());
        
        String sqlIdx5 = "CREATE INDEX idx_FundStocks_fundNumber ON FundStocks(fundnumber)";
        db.execSQL(sqlIdx5.toString());
        
        String sqlIdx6 = "CREATE INDEX idx_FundStocks_stocksnumber ON FundStocks(stocksnumber)";
        db.execSQL(sqlIdx6.toString());
        
        String sqlIdx7 = "CREATE INDEX idx_zzhyfl_number ON zzhyfl(number)";
        db.execSQL(sqlIdx7.toString());
        
        String sqlIdx8 = "CREATE INDEX idx_Stocksfl_number ON Stocksfl(number)";
        db.execSQL(sqlIdx8.toString());
        
        String sqlIdx9 = "CREATE INDEX idx_Stocksfl_level1 ON Stocksfl(level1)";
        db.execSQL(sqlIdx9.toString());
        
        String sqlIdx10 = "CREATE INDEX idx_Stocksfl_level2 ON Stocksfl(level2)";
        db.execSQL(sqlIdx10.toString());
        
        String sqlIdx11 = "CREATE INDEX idx_Stocksfl_level3 ON Stocksfl(level3)";
        db.execSQL(sqlIdx11.toString());
        
        String sqlIdx12 = "CREATE INDEX idx_Stocksfl_level4 ON Stocksfl(level4)";
        db.execSQL(sqlIdx12.toString());
        
        String sqlIdx13 = "CREATE INDEX idx_indexStocks_stocksnumber ON indexStocks(stocksnumber)";
        db.execSQL(sqlIdx13.toString());
        
        String sqlIdx14 = "CREATE INDEX idx_indexStocks_indexnumber ON indexStocks(indexnumber)";
        db.execSQL(sqlIdx14.toString());
        
        String sqlIdx15 = "CREATE INDEX idx_fundTrade_fundNumber ON fundTrade(fundNumber)";
        db.execSQL(sqlIdx15.toString());
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        // 调用时间：如果DATABASE_VERSION值被改为别的数,系统发现现有数据库版本不同,即会调用onUpgrade

        // onUpgrade方法的三个参数，一个 SQLiteDatabase对象，一个旧的版本号和一个新的版本号
        // 这样就可以把一个数据库从旧的模型转变到新的模型
        // 这个方法中主要完成更改数据库版本的操作

        Log.d(Constant.LOG_TAG, "DatabaseHelper onUpgrade");

        StringBuffer sql = new StringBuffer();
        sql.setLength(0);
        sql.append("	create table fundTrade (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	fundNumber TEXT,	\n");
        sql.append("	date TEXT,	\n");
        sql.append("	type INTEGER	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        String sqlIdx1 = "CREATE INDEX idx_fundTrade_fundNumber ON fundTrade(fundNumber)";
        db.execSQL(sqlIdx1.toString());
    }

    @Override
    public void onOpen(SQLiteDatabase db)
    {
        super.onOpen(db);
        // 每次打开数据库之后首先被执行
//        db.execSQL("delete from FundNetValue where fundNumber='XAU' or fundNumber='OIL'");
        Log.d(Constant.LOG_TAG, "DatabaseHelper onOpen");
    }

}